from django.db.models import FilteredRelation, Q, ForeignObject
from django.db.models.sql import Query
from django.db.models.sql.constants import INNER, LOUTER
from django.db.models.sql.datastructures import Join


class QueryUtil:
    """Django查询工具类"""

    class CustomFilteredRelation(FilteredRelation):
        """重写过滤关系类 改成在连接查询时，on过滤模型为右模型"""
        def __init__(self, relation_name, condition=Q(), filter_model=None):
            super().__init__(relation_name, condition=condition)
            # 增加一个query属性，如果传入模型则使用该模型作为过滤模型
            self.query = Query(filter_model) if filter_model is not None else None
            self.alias = filter_model._meta.db_table

        def as_sql(self, compiler, connection):
            # Resolve the condition in Join.filtered_relation.
            query = compiler.query if self.query is None else self.query
            where = query.build_filtered_relation_q(self.condition, reuse=set(self.path))
            return compiler.compile(where)

        def resolve_expression(self, *args, **kwargs):
            raise NotImplementedError('FilteredRelation.resolve_expression() is unused.')

    @classmethod
    def do_join(cls, queryset, join_type, left_model, right_model, joining_columns, right_filters=None):
        """实现表连接

        :param queryset: Queryset
        :param join_type: 连接类型
        :param left_model: 左表模型
        :param right_model: 右表模型
        :param joining_columns: 连接的字段 (("L_A_field", "R_A_field"), ("R_A_field", "R_B_field"))
        :param right_filters: {field: value} 过滤右模型的条件 支持django中的双下划线(__)查询
        :return:
        """
        assert join_type in (INNER, LOUTER)
        if right_filters is None:
            right_filters = {}

        jf = ForeignObject(to=right_model, on_delete=None, from_fields=[], to_fields=[])
        jf.get_joining_columns = lambda: joining_columns

        nullable = True if join_type == LOUTER else False
        # 使用重写的连接过滤类 用于实现过滤右模型的条件
        filtered_relation = cls.CustomFilteredRelation(jf, condition=Q(**right_filters), filter_model=right_model)
        j = Join(right_model._meta.db_table, left_model._meta.db_table, "T1", join_type, jf, nullable,
                 filtered_relation=filtered_relation)
        # 解决因queryset没有调用filter()，而导致alias_map的KeyError异常
        queryset.query.get_initial_alias()
        queryset.query.join(j)

    @classmethod
    def left_join(cls, queryset, left_model, right_model, joining_columns, right_filters=None):
        """orm实现左外连接
        SELECT * FROM left_model LEFT OUTER JOIN right_model ON joining_columns AND right_filters

        :param queryset: Queryset
        :param left_model: 左表模型
        :param right_model: 右表模型
        :param joining_columns: 左外连接的字段 (("L_A_field", "R_A_field"), ("R_A_field", "R_B_field"))
        :param right_filters: {field: value} 过滤右模型的条件  支持django中的双下划线(__)查询
        :return:
        """
        cls.do_join(queryset, LOUTER, left_model, right_model, joining_columns, right_filters)

    @classmethod
    def inner_join(cls, queryset, left_model, right_model, joining_columns, right_filters=None):
        """orm实现内连接
        SELECT * FROM left_model INNER OUTER JOIN right_model ON joining_columns AND right_filters

        :param queryset: Queryset
        :param left_model: 左表模型
        :param right_model: 右表模型
        :param joining_columns: 内连接的字段 (("L_A_field", "R_A_field"), ("R_A_field", "R_B_field"))
        :param right_filters: {field: value} 过滤右模型的条件  支持django中的双下划线(__)查询
        :return:
        """
        cls.do_join(queryset, INNER, left_model, right_model, joining_columns, right_filters)

    @staticmethod
    def handle_underline2_field_sql(table, field_str, value, quote_name_unless_alias, quote_name):
        """实现django通过双下划线拼接查询SQL

        目前只实现"in", "contains", "eq"
        """
        assert field_str, "字段名不能为空"
        arr = field_str.split("__")
        assert len(arr) <= 2, "字段名只能包含一次(__)"
        if len(arr) == 1 or arr[1] == "eq":
            return f"{quote_name_unless_alias(table)}.{quote_name(arr[0])} = {value}"
        field, lookup = arr
        assert lookup in ("in", "contains")
        if lookup == "in":
            assert isinstance(value, (list, tuple, set)), "in查询必须传入列表值"
            value = [f"'{v}'" for v in value]
            return f"{quote_name_unless_alias(table)}.{quote_name(arr[0])} IN ({','.join(value)})"
        elif lookup == "contains":
            return f"{quote_name_unless_alias(table)}.{quote_name(arr[0])} LIKE '%{value}%'"
